﻿<cfsilent>
	<cfscript>
		
		sessionAdvice = getProperty("serviceFactory").getBean("sessionAdvice");
		excel = getProperty("serviceFactory").getBean("spreadSheetObject");
		propAdvice = getProperty("serviceFactory").getBean("schemaPropertyAdvice");
		
		depId = sessionAdvice.getUserProp("teacherDepartment");
		
		subjectId = event.getArg("SubID");
		grade = event.getArg("Edition");
		
		sql = "	SELECT
					s.sbj_id,s.sbj_name
				FROM
					t_subject s
				WHERE
					s.sbj_id = :subjectId ";
		
		
		queryObj = new Query(datasource = application.dnsSlave);

		queryObj.addParam(name="subjectId", value=subjectId, cfsqltype="cf_sql_varchar");
		
		rs_subjectEntity = queryObj.execute(sql=sql).getResult();
		
		
		temFile = GetTempDirectory() & createUUID() & ".xls";
		downFile = URLEncodedFormat( rs_subjectEntity.sbj_name & grade & "版" & "培养方案", "utf-8");
		
		spreadsheetObj = excel.SpreadSheetNew( rs_subjectEntity.sbj_name & grade & "版", false);
		
		excel.SpreadsheetAddrow(spreadsheetObj, "开课学期,课程类别,专业,年级,学位课,是否必修,课程号,课程,学分,任课单位,理论学时,实践学时,上机学时");

		/* 专业培养方案 */
		
		sql = "	SELECT
					s.term_index,
					cn.cna_name,
					sbj.sbj_name,
					cp.prop_name,
					c.course_code,
					c.course_name,
					c.course_credit,
					i.institute_name,
					c.period_theory,
					c.period_practice,
					c.period_computer,
					s.shm_prop
				FROM
					t_schema s
					INNER JOIN t_course_nature cn ON cn.cna_id = s.cna_id
					INNER JOIN t_subject sbj ON sbj.sbj_id = s.sbj_id
					INNER JOIN t_course_prop cp ON cp.prop_id = s.prop_id
					INNER JOIN t_course c ON c.cid = s.cid
					INNER JOIN t_institute i ON i.institute_id = c.institute_id
				WHERE
					s.grade = :grade 
					AND
					sbj.institute_id = :depId 
					AND
					(
					   sbj.sbj_id LIKE :subjectId 
					   OR
					   sbj.sbj_id LIKE :subjectDir 
					)
				ORDER BY
					s.term_index,
					sbj.sbj_id,
					cn.cna_name,
					cp.prop_name,
					c.course_name";
		
		queryObj = new Query(datasource = application.dnsSlave);

		queryObj.addParam(name="grade", value=grade, cfsqltype="cf_sql_char");
		queryObj.addParam(name="subjectId", value=subjectId, cfsqltype="cf_sql_varchar");
		queryObj.addParam(name="depId", value=depId, cfsqltype="cf_sql_varchar");
		queryObj.addParam(name="subjectDir", value=subjectId & "_", cfsqltype="cf_sql_varchar");
		
		rs_schema = queryObj.execute(sql=sql).getResult();
		
		for(a = 1; a LTE rs_schema.recordCount; a++){
			propAdvice.parseProperty( rs_schema['shm_prop'][a] );
			
			rowData = rs_schema['term_index'][a] 
						& "," 
						& rs_schema['cna_name'][a]
						& "," 
						& rs_schema['sbj_name'][a]
						& "," 
						& grade;
			
			if ( propAdvice.getDegreeCourse() ) {
			
				rowData = rowData 
								& "," 
								& "是";
			}
			else {
				
				rowData = rowData 
								& "," 
								& " ";
				
			}
			
			rowData = rowData 
						& "," 
						& rs_schema['prop_name'][a]
						& ","
						& rs_schema['course_code'][a]
						& ","
						& rs_schema['course_name'][a]
						& ","
						& numberFormat( rs_schema['course_credit'][a], "_.__" )
						& ","
						& rs_schema['institute_name'][a];

			if ( rs_schema['period_theory'][a] gt 0 ) {
				rowData = rowData 
							& "," 
							& rs_schema['period_theory'][a];
			}
			else {
				rowData = rowData & ", ";
			}
			
			if ( rs_schema['period_practice'][a] gt 0 ) {
				rowData = rowData 
							& "," 
							& rs_schema['period_practice'][a];
			}
			else {
				rowData = rowData & ", ";
			}
			
			if ( rs_schema['period_computer'][a] gt 0 ) {
				rowData = rowData 
							& "," 
							& rs_schema['period_computer'][a];
			}
			else {
				rowData = rowData & ", ";
			}			
			
			excel.SpreadsheetAddrow(spreadsheetObj, rowData);
			
		}

		excel.SpreadSheetWrite(spreadsheetObj, temFile, true);
		
	</cfscript>
	<cfheader name="Content-Disposition" value="attachment; filename=#downFile#.xls" />
	<cfcontent file="#temFile#" reset="yes" type="application/msexcel" deletefile="yes" />	
</cfsilent>